package py.excelHandle;

import java.io.File;
import java.io.FileFilter;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;

import javax.swing.JTextArea;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import py.SwitchConfig;
import py.filter.FilterManager;
import py.pathMaker.PathMaker;
import py.util.ExcelConfig;
import py.util.ToolUtil;

public class ExcelUtil {
//	ExecutorService executorService = Executors.newFixedThreadPool(1);
//	List<Future<String>> resultList = new ArrayList<Future<String>>();
	private JTextArea processArea=null;
	
	public void doWork(File dirPath,JTextArea processArea) throws InterruptedException, ExecutionException
	{
		PathMaker.setSourceBasePath(dirPath);
		StringBuffer sb = new StringBuffer();
		this.processArea = processArea;
		traverseFolder2(dirPath);

	}
	
	/**
	 * 广度遍历
	 * @param path
	 */
	public void traverseFolder2(File file) {

//        File file = new File(path);
        List<File> dirList=new ArrayList<File>();
        PathMaker  pathMaker =new PathMaker();
        if (file.exists()) {
            File[] files = file.listFiles();
            if (files.length == 0) {
                System.out.println("文件夹是空的!");
                return;
            } else {
                for (File file2 : files) {
                    if (file2.isDirectory()) {
                        System.out.println("文件夹:" + file2.getAbsolutePath());
                        dirList.add(file2);
//                        traverseFolder2(file2.getAbsolutePath());
                    } else {	
                    	String fileName=file2.getName();
                    	if(!fileName.endsWith(".xls")&&!fileName.endsWith(".xlsx"))
                    	{
                    		continue;
                    	}
						I_ExcelCallable exCal = null;
						if (file2.getName().endsWith(".xls"))
							exCal = new ExcelCallable03(file2, pathMaker);
						else
							exCal = new ExcelCallable07(file2, pathMaker);
						try {
							String result = exCal.callsyn();
							processArea.append(result + "\r\n");
							processArea.paintImmediately(processArea
									.getBounds());
						} catch (Exception e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}

//                		}//end for
                    }
                }//end for
        		//将所有文档合并
        		appendTips(processArea, "开始合并文件");
        		try {
        			String resultFilePath=pathMaker.getResultPath(file.getAbsolutePath())+File.separator+"result";
        			mergeFile(processArea,resultFilePath);
        		} catch (Exception e) {
        			// TODO Auto-generated catch block
        			processArea.append(e.getMessage());
        			e.printStackTrace();
        		}
            }
        } else {
            System.out.println("文件不存在!");
        }
        for(File dirFile : dirList)
        {
        	System.out.println("迭代文件夹:" + dirFile.getAbsolutePath());
        	traverseFolder2(dirFile);
        }
    }
	
	public void mergeFile(JTextArea processArea,String  resultFilePath) throws IOException,InterruptedException, InvalidFormatException
	{
		//String resultFilePath=ExcelConfig.parentPath+File.separator+"result";  
		File resultDir=new File(resultFilePath);
		File[] fileList=resultDir.listFiles();         //Files in result path
		
		if(fileList==null)
		{
			return;
		}
		//打开结果Excel文件
//		FileOutputStream fileOut = new FileOutputStream(path);  
		SXSSFWorkbook wwb = new SXSSFWorkbook(1000);   //结果Excel文件
		SXSSFSheet writeableSheet=null;
		
		int resultRowNumber=0;
		//迭代文件夹中的文件
		for(int i=0;i<fileList.length;i++)
		{	
			File f =fileList[i];
//			POIFSFileSystem fs=new POIFSFileSystem(f);
			XSSFWorkbook readBook = new XSSFWorkbook(f);
			Iterator<org.apache.poi.ss.usermodel.Sheet> sheets=readBook.sheetIterator();
			//轮询一个文档中的sheet
			int sleepTimes=1;
			int stNum=0;  //被合并文件的第几个sheet页
			while(sheets.hasNext())
			{
				stNum++;
				int insertRowNum=0;       //本sheet共被插入多少行
				XSSFSheet rst=(XSSFSheet)sheets.next();  //迭代被合并文件的sheet页
				int rowNum=rst.getLastRowNum();
//				if((resultRowNumber+rowNum)>50000*sleepTimes)
//				{
//					System.out.println("Sleepting");
//					Thread.currentThread().sleep(2000);
//					sleepTimes++;
//				}
				//判断是否需要新增文件  超过40万行就新增一个result文件
				if((resultRowNumber+rowNum)>400000)
				{
					wwb.write(createNewFile(resultFilePath));
					wwb.close();
					appendTips(processArea, "已共合并："+resultRowNumber+"行数据，新数据生成中..."+"\r\n");
									
					wwb=new SXSSFWorkbook();
					resultRowNumber=0;
					System.out.println("saved one result file,run GC");
					Runtime.getRuntime().gc();
					Thread.currentThread().sleep(5000);	
				}
				if(wwb.getNumberOfSheets()==0)
				{
					writeableSheet=wwb.createSheet("result");
				}
				//判断是否需要新增文件 end 
				
				for(int r=SwitchConfig.getTitleLine()-1;r<rowNum+1;r++)  //按照每一行迭代
				{
					
					XSSFRow row=rst.getRow(r);  
					int columnNum=row.getLastCellNum();
					//检查空行
					boolean isBlank=true;
					XSSFCell[] cs = new XSSFCell[columnNum];
					for(int colN=0;colN<columnNum;colN++)
					{
						cs[colN]=row.getCell(colN);
						if(((String)ToolUtil.getCellValue(cs[colN])).trim().length()>0)
							isBlank=false;						
					}
					if(isBlank)
						continue;
					//每个文件的SwitchConfig.getTitleLine()-1行为标题行
					if(resultRowNumber!=0&& r==SwitchConfig.getTitleLine()-1)  //跳过被合并文件的第一行
					{
						//lable=new Label(columnNum,resultRowNumber,cs.getDesColName());  //设计标头
						continue;
					}
					else
					{
						
						XSSFCell[] cs2=FilterManager.handleFilter(cs);
						if(cs2!=null)
						{
							Row wr=writeableSheet.createRow(resultRowNumber);   //结果文件中新增一列
							short column =0;
							for (XSSFCell cell : cs2) {  //一行中的所有单元格
                                try{
                                	if(cell==null)
                                		wr.createCell(column, XSSFCell.CELL_TYPE_BLANK);
                                	else
                                	   wr.createCell(column).setCellValue(ToolUtil.getCellValue(cell));
    								
                                }catch(Exception ex)
                                {
                                	ex.printStackTrace();
                                }
                                finally
                                {
                                	column++;
                                }
								
							}//结束一行中每个单元格的添加
						}
						else
							continue;
						
					}
					insertRowNum++;
					resultRowNumber++;
				}//结束一个sheet中每行的迭代
				appendTips(processArea, "完成 ："+f.getName()+" sheet:"+stNum+" 共："+insertRowNum+"行"+"\r\n");
				
			}//结束所有sheet的迭代
			//关闭输入流但不关输出流
			readBook.close();
			readBook=null;
//			is.close();
			
		}//完成整个文件列表
		wwb.write(createNewFile(resultFilePath));;
		wwb.close();
		wwb=null;
		appendTips(processArea, "最终文件共合并："+resultRowNumber+"行数据");
		Thread.currentThread().sleep(5000);	
		Runtime.getRuntime().runFinalization();
	}
	
	private void appendTips(JTextArea processArea,String text)
	{
		processArea.append(text);
		processArea.paintImmediately(processArea.getBounds());
//		try {
//			Thread.currentThread().sleep(500);
//		} catch (InterruptedException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
	}
	
	private FileOutputStream createNewFile(String  resultFilePath) throws IOException
	{
		Calendar c=Calendar.getInstance();
		String nowTime=c.get(Calendar.HOUR_OF_DAY)+"."+c.get(Calendar.MINUTE)+"."+c.get(Calendar.SECOND);
		File resultDir=new File(resultFilePath);
		FileOutputStream resultFileStream=new FileOutputStream(resultFilePath+File.separator+"result-"+nowTime+".xlsx");
		
		return resultFileStream;
	}

}
